﻿using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;

namespace com.uplus.data
{
    /// <summary>
    /// MsSql数据库访问类
    /// (本类需要存储过程, 创建语句请见嵌入资源:SQLSERVER通用存储过程.sql)
    /// </summary>
    [Serializable]
    public class DAMsSql : IDaAccess
    {
        public SqlSugarClient SqlSugarClient = null;

        #region == DAMsSql ==
        public DAMsSql(string connectString)
        {
            CreateConnection(connectString);
        }

        void CreateConnection(string connString)
        {
            SqlSugarClient = new SqlSugarClient(new ConnectionConfig { ConnectionString = connString, DbType = DbType.MySql, IsAutoCloseConnection = true });
        }

        #endregion

        #region == Excute ==
        public int Excute(string sql, Dictionary<string, object> paramValues)
        {
            int n = 0;
            try
            {
                n = SqlSugarClient.Ado.ExecuteCommand(sql, paramValues);
            }
            catch (Exception ex)
            {
                return n;
                throw ex;
            }
            return n;
        }
        public int Excute(string sql)
        {
            return this.Excute(sql, null);
        }

        public int Excute(string sql, string paramName, object paramValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(paramName, paramValue);
            return this.Excute(sql, pm);
        }
        #endregion

        #region == GetValue<T>
        public T GetValue<T>(string sql)
        {
            return this.GetValue<T>(sql, null);
        }

        public T GetValue<T>(string sql, string paramName, object paramValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(paramName, paramValue);
            return this.GetValue<T>(sql, pm);
        }

        public T GetValue<T>(string sql, Dictionary<string, object> paramValues)
        {
            object re = null;
            try
            {
                re = SqlSugarClient.Ado.GetScalar(sql, paramValues);
                re = Convert.ChangeType(re, typeof(T));
            }
            catch (Exception ex)
            {
                return (T)re;
                throw ex;
            }
            return (T)re;
        }
        #endregion

        #region == GetValueTable<T>
        public T GetValueTable<T>(string table, string colName)
        {
            return this.GetValueTable<T>(table, colName, null);
        }

        public T GetValueTable<T>(string table, string colName, string colWhereName, object colWhereValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(colWhereName, colWhereValue);
            return this.GetValueTable<T>(table, colName, pm);
        }

        public T GetValueTable<T>(string table, string colName, Dictionary<string, object> colValues)
        {
            object re = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat($"select {colName} from {table}");
                if (colValues != null)
                {
                    sb.Append(" where 1=1");
                    foreach (KeyValuePair<string, object> item in colValues)
                    {
                        sb.Append($" and {item.Key}=@{item.Key}");
                    }
                }
                re = SqlSugarClient.Ado.GetScalar(sb.ToString(), colValues);
                re = Convert.ChangeType(re, typeof(T));
            }
            catch (Exception ex)
            {
                return (T)re;
                throw ex;
            }
            return (T)re;
        }
        #endregion

        #region == GetList ==
        public List<T> GetModelList<T>(string sql) where T : class, new()
        {
            return SqlSugarClient.SqlQueryable<T>(sql).ToList();

        }
        /// <summary>
        /// 获取实体类集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Model"></param>
        /// <param name="expression">条件表达式</param>
        /// <returns></returns>
        public List<T> GetModelList<T>(Expression<Func<T, bool>> expression = null) where T : class, new()
        {
            List<T> List;
            if (expression == null)
            {
                List = SqlSugarClient.Queryable<T>().ToList();
            }
            else
            {
                List = SqlSugarClient.Queryable<T>().Where(expression).ToList();
            }
            return List;
        }
        /// <summary>
        /// 异步获取实体集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Model"></param>
        /// <param name="expression">条件表达式</param>
        /// <returns></returns>
        public Task<List<T>> GetModelListAsync<T>(Expression<Func<T, bool>> expression = null) where T : class, new()
        {
            Task<List<T>> List;
            if (expression == null)
            {
                List = SqlSugarClient.Queryable<T>().ToListAsync();
            }
            else
            {
                List = SqlSugarClient.Queryable<T>().Where(expression).ToListAsync();
            }
            return List;
        }
        public System.Data.DataTable GetList(string sql)
        {
            return GetList(sql, null);
        }

        public System.Data.DataTable GetList(string sql, string paramName, object paramValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(paramName, paramValue);
            return this.GetList(sql, pm);
        }

        public System.Data.DataTable GetList(string sql, Dictionary<string, object> paramValues)
        {
            System.Data.DataTable Dtb = new System.Data.DataTable();
            try
            {
                //System.Data.DataTable Dt = SqlSugarClient.Ado.GetDataTable(sql, paramValues);
                //List<string> pam = new List<string>();
                //for (int tmp = 0; tmp < Dt.Columns.Count; tmp++)
                //{
                //    pam.Add(Dt.Columns[tmp].ColumnName);
                //    Dtb.Columns.Add(Dt.Columns[tmp].ColumnName);
                //}
                //foreach (SqlSugar.DataRow item in Dt.Rows)
                //{
                //    System.Data.DataRow dr = Dtb.NewRow();
                //    foreach (string itemCol in pam)
                //    {
                //        dr[itemCol] = item[itemCol];
                //    }
                //    Dtb.Rows.Add(dr);
                //}
                Dtb = SqlSugarClient.Ado.GetDataTable(sql, paramValues);
            }
            catch (Exception ex)
            {
                return Dtb;
                throw ex;
            }
            return Dtb;
        }

        public System.Data.SqlClient.SqlDataReader GetReader(string sql)
        {
            try
            {
                return SqlSugarClient.Ado.GetDataReader(sql) as System.Data.SqlClient.SqlDataReader;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
        }
        #endregion

        #region == Insert ==
        public int Insert(string tableName, Dictionary<string, object> colvalues)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" insert into {0} (", tableName);
            string flag = "";
            foreach (KeyValuePair<string, object> item in colvalues)
            {
                sb.AppendFormat("{0}{1}", flag, item.Key);
                flag = ",";
            }
            sb.Append(") Values (");
            flag = "";
            foreach (KeyValuePair<string, object> item in colvalues)
            {
                sb.AppendFormat("{0}@{1}", flag, item.Key);
                flag = ",";
            }
            sb.Append(")");
            return this.Excute(sb.ToString(), colvalues);
        }
        public int InsertModelList<T>(List<T> models) where T : class, new()
        {
            try
            {
                return SqlSugarClient.Insertable(models).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int InsertModel<T>(T model) where T : class, new()
        {
            try
            {
                return SqlSugarClient.Insertable(model).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int InsertModelIgnore<T>(T model, Expression<Func<T, object>> ignoreColumns) where T : class, new()
        {
            try
            {
                return SqlSugarClient.Insertable(model).IgnoreColumns(ignoreColumns).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void InsertModelListAsync<T>(List<T> models) where T : class, new()
        {
            try
            {
                SqlSugarClient.Insertable(models).ExecuteCommandAsync();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void InsertModelAsync<T>(T model) where T : class, new()
        {
            try
            {
                SqlSugarClient.Insertable(model).ExecuteCommandAsync();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertReturnIdentity<T>(T model) where T : class, new()
        {
            int t = SqlSugarClient.Insertable(model).ExecuteReturnIdentity();
            return t;
        }
        public string InsertForId(string tableName, Dictionary<string, object> colvalues)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" insert into {0} (", tableName);
            string flag = "";
            foreach (KeyValuePair<string, object> item in colvalues)
            {
                sb.AppendFormat("{0}{1}", flag, item.Key);
                flag = ",";
            }
            sb.Append(") Values (");
            flag = "";
            foreach (KeyValuePair<string, object> item in colvalues)
            {
                sb.AppendFormat("{0}@{1}", flag, item.Key);
                flag = ",";
            }
            sb.Append(")select @@identity");
            return SqlSugarClient.Ado.GetScalar(sb.ToString(), colvalues).ToString();
        }
        #endregion

        #region == Update ==
        public int Update(string table, Dictionary<string, object> colValues, Dictionary<string, object> where)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" update {0} set ", table);
            string flag = "";
            foreach (KeyValuePair<string, object> item in colValues)
            {
                sb.AppendFormat("{0}{1}={2}{1}", flag, item.Key, "@");
                flag = ",";
                pm.Add(item.Key, item.Value);
            }
            flag = " where ";
            foreach (KeyValuePair<string, object> item in where)
            {
                sb.AppendFormat("{0}{1}={2}{1}_2", flag, item.Key, "@");
                flag = " and ";
                pm.Add(item.Key + "_2", item.Value);
            }
            return this.Excute(sb.ToString(), pm);
        }

        public int Update(string tableName, string pkcol, object pkValue, Dictionary<string, object> colvalues)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" update {0} set ", tableName);
            string flag = "";
            foreach (KeyValuePair<string, object> item in colvalues)
            {
                sb.AppendFormat("{0}{1}={2}{1}", flag, item.Key, "@");
                flag = ",";
            }
            // 防止更新PKID时出错, 将条件中的@PKID更名
            sb.AppendFormat(" where {0}={1}{0}_2 ", pkcol, "@");
            colvalues.Add(string.Format("{0}_2", pkcol), pkValue);
            return this.Excute(sb.ToString(), colvalues);
        }

        public int Update(string table, Dictionary<string, object> colvalues, string pkcol, object pkValue)
        {
            Dictionary<string, object> where = new Dictionary<string, object>();
            where.Add(pkcol, pkValue);
            return this.Update(table, colvalues, where);
        }

        public int Update(string table, string colName, object colNameValue, string colWhere, object colWhereValue)
        {
            Dictionary<string, object> where = new Dictionary<string, object>();
            where.Add(colWhere, colWhereValue);
            Dictionary<string, object> colvalues = new Dictionary<string, object>();
            colvalues.Add(colName, colNameValue);
            return this.Update(table, colvalues, where);
        }

        /// <summary>
        /// 更新所有字段
        /// </summary>        
        /// <param name="model">新的实体</param>
        /// <param name="expression">条件表达式，默认为空</param>
        /// <returns>更新数量</returns>
        public int UpdateModel<T>(T model, Expression<Func<T, bool>> expression = null) where T : class, new()
        {
            try
            {
                int t1;
                if (expression == null)
                    t1 = SqlSugarClient.Updateable(model).ExecuteCommand();
                else
                    t1 = SqlSugarClient.Updateable(model).Where(expression).ExecuteCommand();
                return t1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 异步更新所有字段
        /// </summary>        
        /// <param name="model">新的实体</param>
        /// <param name="expression">条件表达式，默认为空</param>
        /// <returns>更新数量</returns>
        public Task<int> UpdateModelAsync<T>(T model, Expression<Func<T, bool>> expression = null) where T : class, new()
        {
            try
            {
                Task<int> t1;
                if (expression == null)
                    t1 = SqlSugarClient.Updateable(model).ExecuteCommandAsync();
                else
                    t1 = SqlSugarClient.Updateable(model).Where(expression).ExecuteCommandAsync();
                return t1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 不更新部分字段
        /// </summary>        
        /// <param name="model">新的实体</param>
        /// <param name="updateColumns">需要更新的列</param>
        /// <param name="expression">条件表达式，默认为空</param>
        /// <returns>更新数量</returns>
        public int UpdateModelIgnore<T>(T model, Expression<Func<T, object>> ignoreColumns, Expression<Func<T, bool>> expression) where T : class, new()
        {
            try
            {
                int t1;
                if (expression == null)
                    t1 = SqlSugarClient.Updateable(model).IgnoreColumns(ignoreColumns).ExecuteCommand();
                else
                    t1 = SqlSugarClient.Updateable(model).IgnoreColumns(ignoreColumns).Where(expression).ExecuteCommand();
                return t1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新部分字段
        /// </summary>        
        /// <param name="model">新的实体</param>
        /// <param name="updateColumns">需要更新的列</param>
        /// <param name="expression">条件表达式，默认为空</param>
        /// <returns>更新数量</returns>
        public int UpdateModel<T>(T model, Expression<Func<T, object>> updateColumns, Expression<Func<T, bool>> expression) where T : class, new()
        {
            try
            {
                int t1;
                if (expression == null)
                    t1 = SqlSugarClient.Updateable(model).UpdateColumns(updateColumns).ExecuteCommand();
                else
                    t1 = SqlSugarClient.Updateable(model).UpdateColumns(updateColumns).Where(expression).ExecuteCommand();
                return t1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 异步更新部分字段
        /// </summary>        
        /// <param name="model">新的实体</param>
        /// <param name="updateColumns">需要更新的列</param>
        /// <param name="expression">条件表达式，默认为空</param>
        /// <returns>更新数量</returns>
        public Task<int> UpdateModelAsync<T>(T model, Expression<Func<T, object>> updateColumns, Expression<Func<T, bool>> expression) where T : class, new()
        {
            try
            {
                Task<int> t1;
                if (expression == null)
                    t1 = SqlSugarClient.Updateable(model).UpdateColumns(updateColumns).ExecuteCommandAsync();
                else
                    t1 = SqlSugarClient.Updateable(model).UpdateColumns(updateColumns).Where(expression).ExecuteCommandAsync();
                return t1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新多个实体所有字段
        /// </summary>        
        /// <param name="model">List实体</param>
        /// <returns>更新数量</returns>
        public int UpdateModelList<T>(List<T> model) where T : class, new()
        {
            try
            {
                int t1 = SqlSugarClient.Updateable(model).ExecuteCommand();
                return t1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 异步更新多个实体所有字段
        /// </summary>        
        /// <param name="model">List实体</param>
        /// <returns>更新数量</returns>
        public Task<int> UpdateModelListAsync<T>(List<T> model) where T : class, new()
        {
            try
            {
                Task<int> t1 = SqlSugarClient.Updateable(model).ExecuteCommandAsync();
                return t1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新多个实体部分字段
        /// </summary>        
        /// <param name="model">新的实体</param>
        /// <param name="updateColumns">需要更新的列</param>
        /// <returns>更新数量</returns>
        public int UpdateModelList<T>(List<T> model, Expression<Func<T, object>> updateColumns) where T : class, new()
        {
            try
            {
                int t1 = SqlSugarClient.Updateable(model).UpdateColumns(updateColumns).ExecuteCommand();
                return t1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 异步更新多个实体部分字段
        /// </summary>        
        /// <param name="model">新的实体</param>
        /// <param name="updateColumns">需要更新的列</param>
        /// <returns>更新数量</returns>
        public Task<int> UpdateModelListAsync<T>(List<T> model, Expression<Func<T, object>> updateColumns) where T : class, new()
        {
            try
            {
                Task<int> t1 = SqlSugarClient.Updateable(model).UpdateColumns(updateColumns).ExecuteCommandAsync();
                return t1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region == Delete ==
        public int Delete(string table, string pkColumn, params object[] values)
        {
            Dictionary<string, object> pam = new Dictionary<string, object>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("delete from {0} where {1}", table, pkColumn);
            if (values.Length == 1)
            {
                sb.AppendFormat("={0}{1}", "@", pkColumn);
                pam.Add(pkColumn, values[0]);
            }
            else
            {
                sb.Append(" in (");
                string flag = "";
                for (int i = 0; i < values.Length; i++)
                {
                    sb.AppendFormat("{0}{1}{2}{3}", flag, "@", pkColumn, i);
                    pam.Add(pkColumn + i, values[i]);
                    flag = ",";
                }
                sb.Append(")");
            }
            return this.Excute(sb.ToString(), pam);
        }

        public int Delete(string table, Dictionary<string, object> colValues)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("delete from {0}", table);

            if (colValues != null && colValues.Count > 0)
            {
                sb.Append(" where 1=1");
                foreach (KeyValuePair<string, object> item in colValues)
                {
                    sb.AppendFormat(" and {0}={1}{0}", item.Key, "@");
                }
            }
            return this.Excute(sb.ToString(), colValues);
        }

        /// <summary>
        /// 删除实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="expression">条件表达式</param>
        /// <returns></returns>
        public int DeleteModel<T>(Expression<Func<T, bool>> expression = null) where T : class, new()
        {
            var t1 = SqlSugarClient.Deleteable<T>().Where(expression).ExecuteCommand();
            return t1;
        }

        /// <summary>
        /// 异步删除实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="expression">条件表达式</param>
        /// <returns></returns>
        public Task<int> DeleteModelAsync<T>(Expression<Func<T, bool>> expression = null) where T : class, new()
        {
            var t1 = SqlSugarClient.Deleteable<T>().Where(expression).ExecuteCommandAsync();
            return t1;
        }
        #endregion

        #region == Get ==
        /// <summary>
        /// 获取实体类集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Model"></param>
        /// <param name="expression">条件表达式</param>
        /// <returns></returns>
        public T GetModel<T>(Expression<Func<T, bool>> expression = null) where T : class, new()
        {
            T model;
            if (expression == null)
            {
                model = SqlSugarClient.Queryable<T>().First();
            }
            else
            {
                model = SqlSugarClient.Queryable<T>().Where(expression).First();
            }
            return model;
        }
        public T GetModel<T>(string sql) where T : class, new()
        {
            return SqlSugarClient.SqlQueryable<T>(sql).First();
        }
        public DmRecord Get(string table, string pkColumn, object value)
        {
            Dictionary<string, object> pam = new Dictionary<string, object>();
            pam.Add(pkColumn, value);
            return this.Get(table, pam);
        }
        public object GetObj(string sql)
        {
            return this.SqlSugarClient.Ado.GetScalar(sql);
        }
        public DmRecord Get(string table, Dictionary<string, object> colValues)
        {
            string sql = string.Format("select * from {0} where 1=1", table);
            foreach (KeyValuePair<string, object> item in colValues)
            {
                sql += string.Format(" and {0}{1}={1}", "@", item.Key);
            }
            return new DmRecord(this.GetList(sql, colValues));
        }

        public DmRecord Get(string sql)
        {
            return new DmRecord(this.GetList(sql));
        }
        #endregion

        #region == Count ==
        public int Count(string table)
        {
            return this.Count(table, null);
        }
        public int Count(string table, string colName, object colValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(colName, colValue);
            return this.Count(table, pm);
        }

        public int Count(string table, Dictionary<string, object> colValues)
        {
            return this.GetValueTable<int>(table, "COUNT(1)", colValues);
        }

        public int CountSql(string sql)
        {
            return this.CountSql(sql, null);
        }

        public int CountSql(string sql, string paramName, object paramValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(paramName, paramValue);
            return this.CountSql(sql, pm);
        }

        public int CountSql(string sql, Dictionary<string, object> paramValues)
        {
            return this.GetValue<int>(string.Format("select COUNT(1) from ({0}) tb", sql), paramValues);
        }
        #endregion

        #region == Exists ==
        public bool Exists(string tb)
        {
            return this.Count(tb) > 0;
        }

        public bool Exists(string tb, string colName, object value)
        {
            return this.Count(tb, colName, value) > 0;
        }

        public bool Exists(string tb, Dictionary<string, object> paramValues)
        {
            return this.Count(tb, paramValues) > 0;
        }

        public bool ExistsSql(string sql)
        {
            return this.CountSql(sql) > 0;
        }

        public bool ExistsSql(string sql, string paramName, object paramValue)
        {
            return this.CountSql(sql, paramName, paramValue) > 0;
        }

        public bool ExistsSql(string sql, Dictionary<string, object> paramValues)
        {
            return this.CountSql(sql, paramValues) > 0;
        }
        #endregion

        #region == Page ==
        public System.Data.DataTable Page(string sql, int pageNum, int pageSize, ref int recordCount)
        {
            System.Data.DataTable Dtb = new System.Data.DataTable();
            try
            {
                //SqlSugar.DataTable Dt = SqlSugarClient.SqlQueryable<object>(sql).ToDataTablePage(pageNum, pageSize, ref recordCount);
                //Dictionary<string, Type> pam = new Dictionary<string, Type>();
                //foreach (DataColumn tmp in Dt.Columns)
                //{
                //    pam.Add(tmp.ColumnName, tmp.DataType);
                //    Dtb.Columns.Add(tmp.ColumnName, tmp.DataType);
                //}
                //foreach (SqlSugar.DataRow item in Dt.Rows)
                //{
                //    System.Data.DataRow dr = Dtb.NewRow();
                //    foreach (string itemCol in pam.Keys)
                //    {
                //        dr[itemCol] = item[itemCol];
                //    }
                //    Dtb.Rows.Add(dr);
                //}
                Dtb = SqlSugarClient.SqlQueryable<object>(sql).ToDataTablePage(pageNum, pageSize, ref recordCount);
            }
            catch (Exception ex)
            {
                return Dtb;
                throw ex;
            }
            return Dtb;
        }
        public List<T> PageList<T>(string sql, int pageNum, int pageSize, ref int recordCount) where T : class, new()
        {
            List<T> list = new List<T>();
            try
            {
                list = SqlSugarClient.SqlQueryable<T>(sql).ToPageList(pageNum, pageSize, ref recordCount);
            }
            catch (Exception ex)
            {
                return list;
                throw ex;
            }
            return list;
        }

        public List<T> PageList<T>(string sql, int pageNum, int pageSize, ref int recordCount, ref int totalPage) where T : class, new()
        {
            List<T> list = new List<T>();
            try
            {
                list = SqlSugarClient.SqlQueryable<T>(sql).ToPageList(pageNum, pageSize, ref recordCount, ref totalPage);
            }
            catch (Exception ex)
            {
                return list;
                throw ex;
            }
            return list;
        }
        #endregion

        #region== ProcCommand==
        public System.Data.DataTable GetProcTableOutPut(string procName, Dictionary<string, string> param, out int pageCount, out int recordCount, int pageSize, int papeIndex)
        {
            System.Data.DataTable Dtb = new System.Data.DataTable();
            try
            {
                List<SugarParameter> parameters = new List<SugarParameter>();
                if (param?.Count > 0)
                {
                    foreach (var item in param)
                    {
                        parameters.Add(new SugarParameter(item.Key, item.Value));
                    }
                }
                var pageCountParam = new SugarParameter("@PageCount", null, true);
                var recordCountParam = new SugarParameter("@RecordCount", null, true);
                parameters.Add(pageCountParam);
                parameters.Add(recordCountParam);
                parameters.Add(new SugarParameter("@PageSize", pageSize));
                parameters.Add(new SugarParameter("@PapeIndex", papeIndex));
                //DataTable Dt = SqlSugarClient.Ado.UseStoredProcedure().GetDataTable(procName, parameters);
                Dtb = SqlSugarClient.Ado.UseStoredProcedure().GetDataTable(procName, parameters);
                pageCount = Convert.ToInt32(pageCountParam.Value);
                recordCount = Convert.ToInt32(recordCountParam.Value);

                //Dictionary<string, Type> pam = new Dictionary<string, Type>();
                //foreach (DataColumn tmp in Dt.Columns)
                //{
                //    pam.Add(tmp.ColumnName, tmp.DataType);
                //    Dtb.Columns.Add(tmp.ColumnName, tmp.DataType);
                //}
                //foreach (SqlSugar.DataRow item in Dt.Rows)
                //{
                //    System.Data.DataRow dr = Dtb.NewRow();
                //    foreach (string itemCol in pam.Keys)
                //    {
                //        dr[itemCol] = item[itemCol];
                //    }
                //    Dtb.Rows.Add(dr);
                //}
            }
            catch (Exception ex)
            {
                pageCount = 0;
                recordCount = 0;
                return Dtb;
                throw ex;
            }
            return Dtb;
        }
        #endregion

    }
}
